package com.controlexpenses.dao;


import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.sql.DataSource;

import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.SqlParameter;
import org.springframework.jdbc.core.support.JdbcDaoSupport;
import org.springframework.jdbc.object.MappingSqlQuery;

import com.controlexpenses.domain.Functionary;

public class FunctionaryDAOJDBC2 extends JdbcDaoSupport implements
		FunctionaryDAO {

	private FuncQuery funcQuery;
	
	private FuncQueryById queryById;
	
	private FuncQuery getFuncQuery() {
		if (funcQuery == null) {
			funcQuery = new FuncQuery(getDataSource());
		}
		return funcQuery;
	}
	
	private FuncQueryById getQueryById() {
		if (queryById == null) {
			queryById = new FuncQueryById(getDataSource());
		}
		return queryById;
	}

	public void delete(Long id) {
		JdbcTemplate jdbcTemplate = this.getJdbcTemplate();
		jdbcTemplate.update(
				"delete from functionary where id=? ",
			     new Object[] {id});
	}

	public Functionary findById(Long id) {
		JdbcTemplate jdbcTemplate = this.getJdbcTemplate();
		
		Map params = new HashMap();
		params.put("id", id);
		
		return (Functionary) getQueryById().findObject(id.longValue());
	}

//	public List findFunctionary() {
//		return this.getFuncQuery().execute();
//	}
	
	public List findFunctionary() {
		JdbcTemplate jdbcTemplate = this.getJdbcTemplate();
		return jdbcTemplate.queryForList("SELECT id, name, salary FROM functionary ORDER BY name");
	}
	

	public Long insert(Functionary f) {
		JdbcTemplate jdbcTemplate = this.getJdbcTemplate();
		jdbcTemplate.update(
			     "INSERT INTO functionary (name, salary) VALUES (?, ?)",
			     new Object[] {
			       f.getName(),
			       new Double(f.getSalary())
			     },
			     new int[] {Types.VARCHAR, Types.NUMERIC});
		
		int id = findIdByName(f.getName());
		f.setId(new Long(id));
		
		//long id = jdbcTemplate.queryForLong("call identity()");
		//f.setId(new Long(id));

		return f.getId();
	}
	
	private int findIdByName(String name) {
		JdbcTemplate jdbcTemplate = this.getJdbcTemplate();
		return jdbcTemplate.queryForInt("" +
				"SELECT id FROM functionary where name=?", 
				new Object[] {name} );
	}

	public void update(Functionary f) {
		JdbcTemplate jdbcTemplate = this.getJdbcTemplate();
		jdbcTemplate.update(
			     "update functionary set name = ?, salary = ? where id = ?",
			     new Object[] {
			       f.getName(),
			       new Double(f.getSalary()),
			       f.getId()
			     });
	}

	
	
	protected class FuncQuery extends MappingSqlQuery {
		/**
		 * Construtor.
		 * @param ds o DataSource usado na consulta
		 * @param sql SQL usado na consulta
		 */
		protected FuncQuery(DataSource ds, String sql) {
			super(ds, sql);
		}

		/**
		 * Construtor.
		 * @param ds o DataSource usado na consulta
		 */
		protected FuncQuery(DataSource ds) {
			super(ds, "SELECT id, name, salary FROM functionary ORDER BY name");
			compile();
		}

		protected Object mapRow(ResultSet rs, int rownum) throws SQLException {
			Functionary f = new Functionary();
			f.setId(new Long(rs.getInt("id")));
			f.setName(rs.getString("name"));
			f.setSalary(rs.getDouble("salary"));
			return f;
		}
	}
	
	protected class FuncQueryById extends FuncQuery {

		/**
		 * Construtor.
		 * @param ds o DataSource usado na consulta
		 */
		protected FuncQueryById(DataSource ds) {
			super(ds, "SELECT id,name,salary FROM functionary where id=:id");
			declareParameter(new SqlParameter(Types.INTEGER));
			compile();
		}

	}
	
}


